﻿create table [Year]
(
	ID int not null primary key,
	Value int not null
)
create table [State]
(
	ID int not null primary key identity(0,1),
	Name nvarchar(MAX) not null
);
create table [Region]
(
	ID int not null primary key identity(0,1),
	Name nvarchar(MAX) not null,
	StateId int not null foreign key references State(ID)
)
create table [City]
(
	ID int not null primary key identity(0,1),
	Name nvarchar(MAX) not null,
	StateId int not null foreign key references State(ID)
);
create table [UniversityType]
(
	ID int not null primary key identity(0,1),
	Value nvarchar(MAX) not null
);
create table [University](
	ID int not null primary key identity(0,1),
	Name nvarchar(MAX),
	Abbreviation nvarchar(50),
	Type int foreign key references UniversityType(ID),
	State int foreign key references State(ID),
	City int foreign key references City(ID),
	Region int foreign key references Region(ID),
	Address nvarchar(MAX),
	Location nvarchar(MAX),
	ShortInfo nvarchar(MAX),
	Accreditation nvarchar(MAX),
	LogoPath nvarchar(MAX),
	PicsPath nvarchar(MAX),
	Phone nvarchar(MAX),
	Email nvarchar(MAX),
	StudentsAmount int,
	TeachersAmount int, 
	CandidatesScAmount int,
	PhDAmount int, 
	ApplyingInfo nvarchar(MAX),
	UnivDetailedInfo nvarchar(MAX),
	SourceLink nvarchar(MAX),
	CostOfTraining nvarchar(MAX),
	FormOfStudying nvarchar(MAX),
	FreeStudying bit,
	Hostel bit,
	IsPrivate bit,
	MilitaryDepartment bit,
	PhD bit,
	Ratings nvarchar(MAX),
	Website nvarchar(MAX),
	PostGraduateEducation bit, 
	TrainingDepartment bit,
	PreparationCourses bit,
	PostalIndex nvarchar(MAX),
	YearId int
);
create table [Faculty](
	ID int not null primary key,
	UniversityID int not null foreign key references University(ID),
	Name nvarchar(MAX) not null,
	YearId int not null
);
create table [EQGroup]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null
)
create table [EQ]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	EQGroupId int not null foreign key references EQGroup(ID),
	YearId int not null foreign key references Year(ID)
)
create table [Branch]
(
	ID int not null primary key,
	EQGroupId int not null foreign key references EQGroup(ID),
	Name nvarchar(MAX) not null,
	YearId int not null foreign key references Year(ID)
)
create table [DirectionType]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null
)
create table [Direction]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	BranchId int not null foreign key references Branch(ID),
	YearId int not null foreign key references Year(ID),
	Code nvarchar(MAX) not null,
	DirectionTypeId int not null foreign key references DirectionType(ID)
)
create table [Subject]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	YearId int not null foreign key references Year(ID)
)
create table [EducationForm]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null
)
create table [Speciality]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	BranchId int not null foreign key references Branch(ID),
	YearId int not null foreign key references Year(ID),
	Code nvarchar(MAX) not null
)
create table [Specialization]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	YearId int not null foreign key references Year(ID),
	DirectionId int not null foreign key references Direction(ID),
	SpecialityId int not null foreign key references Speciality(ID),
	BranchId int not null foreign key references Branch(ID)
)
create table [AnnualSet]
(
	ID int not null primary key,
	FacultyId int not null foreign key references Faculty(ID),
	DirectionId int not null foreign key references Direction(ID),
	SpecialityId int not null foreign key references Speciality(ID),
	EQId int not null foreign key references EQ(ID),
	YearId int not null foreign key references Year(ID),
	EducationForm int not null foreign key references EducationForm(ID),
	SpecializationId int not null foreign key references Specialization(ID),
	OfferedPlaces int,
	ScholarshipPlaces int,
	VstupId int not null
)
create table [AnnualSetSubject]
(
	ID int not null primary key,
	AnnualSetId int not null foreign key references AnnualSet(ID),
	SubjectId int not null foreign key references Subject(ID),
	IsCoreSubject bit not null
)
create table [Gallery]
(
	ID int not null primary key identity(0,1),
	UniversityId int not null foreign key references University(ID),
	ImageName nvarchar(MAX) not null
)
create table [ProfessionGroup]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null
)
create table [Profession]
(
	ID int not null primary key,
	Name nvarchar(MAX) not null,
	ProfessionGroupId int not null foreign key references ProfessionGroup(ID),
	Info nvarchar(MAX) not null,
	PlaceOfJob nvarchar(MAX) not null,
	PersonalCharacteristics nvarchar(MAX) not null,
	Career nvarchar(MAX) not null,
	Description nvarchar(MAX) not null,
	SourceLink nvarchar(200) not null
)
